﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace BanHangCshap.Class
{

    class HangHoa:Database
    {
        #region ***** Fields & Properties *****
        private string _mahang;
        public string mahang
        {
            get
            {
                return _mahang;
            }
            set
            {
                _mahang = value;
            }
        }
        private string _mavach;
        public string mavach
        {
            get
            {
                return _mavach;
            }
            set
            {
                _mavach = value;
            }
        }
        private string _tenhang;
        public string tenhang
        {
            get
            {
                return _tenhang;
            }
            set
            {
                _tenhang = value;
            }
        }
        private string _dvt;
        public string dvt
        {
            get
            {
                return _dvt;
            }
            set
            {
                _dvt = value;
            }
        }
        private string _dvt1;
        public string dvt1
        {
            get
            {
                return _dvt1;
            }
            set
            {
                _dvt1 = value;
            }
        }
        private int _tilequydoi1;
        public int tilequydoi1
        {
            get
            {
                return _tilequydoi1;
            }
            set
            {
                _tilequydoi1 = value;
            }
        }
        private string _dvt2;
        public string dvt2
        {
            get
            {
                return _dvt2;
            }
            set
            {
                _dvt2 = value;
            }
        }
        private int _tilequydoi2;
        public int tilequydoi2
        {
            get
            {
                return _tilequydoi2;
            }
            set
            {
                _tilequydoi2 = value;
            }
        }
        private string _mota;
        public string mota
        {
            get
            {
                return _mota;
            }
            set
            {
                _mota = value;
            }
        }
        private double _gianhap;
        public double gianhap
        {
            get
            {
                return _gianhap;
            }
            set
            {
                _gianhap = value;
            }
        }
        private double _giaxuatsi;
        public double giaxuatsi
        {
            get
            {
                return _giaxuatsi;
            }
            set
            {
                _giaxuatsi = value;
            }
        }
        private double _giaxuatle;
        public double giaxuatle
        {
            get
            {
                return _giaxuatle;
            }
            set
            {
                _giaxuatle = value;
            }
        }
        private int _tondinhmuc;
        public int tondinhmuc
        {
            get
            {
                return _tondinhmuc;
            }
            set
            {
                _tondinhmuc = value;
            }
        }
        
        private string _ghichu;
        public string ghichu
        {
            get
            {
                return _ghichu;
            }
            set
            {
                _ghichu = value;
            }
        }
        private string _maloai;
        public string maloai
        {
            get
            {
                return _maloai;
            }
            set
            {
                _maloai = value;
            }
        }
        private int _tgbaohanh;
        public int tgbaohanh
        {
            get
            {
                return _tgbaohanh;
            }
            set
            {
                _tgbaohanh = value;
            }
        }
        private bool _ngungtheodoi;
        public bool ngungtheodoi
        {
            get
            {
                return _ngungtheodoi;
            }
            set
            {
                _ngungtheodoi = value;
            }
        }
        private int _sltonhientai;
        public int sltonhientai
        {
            get
            {
                return _sltonhientai;
            }
            set
            {
                _sltonhientai = value;
            }
        }
        private double _tgtonhientai;
        public double tgtonhientai
        {
            get
            {
                return _tgtonhientai;
            }
            set
            {
                _tgtonhientai = value;
            }
        }
        #endregion

        #region ***** Init Methods *****
        public HangHoa()
        {
        }
        public HangHoa(string mahang)
        {
            string sql = "select * from HangHoa where mahang = '" + mahang + "'";
            foreach (DataRow dr in getDataTable(sql).Rows)
            {
                this.mahang = dr["mahang"].ToString().Trim();
                this.tenhang = dr["tenhang"].ToString().Trim();
                this.dvt = dr["dvt"].ToString().Trim();
                this.dvt1 = dr["dvt1"].ToString().Trim();
                this.tilequydoi1 = int.Parse(dr["tilequydoi1"].ToString());
                this.dvt2 = dr["dvt2"].ToString().Trim();
                this.tilequydoi2 = int.Parse(dr["tilequydoi2"].ToString());
                this.mota = dr["mota"].ToString().Trim();
                this.gianhap = double.Parse(dr["gianhap"].ToString().Trim());
                this.giaxuatsi = double.Parse(dr["giaxuatsi"].ToString().Trim());
                this.giaxuatle = double.Parse(dr["giaxuatle"].ToString().Trim());
                this.tondinhmuc = int.Parse(dr["tondinhmuc"].ToString().Trim());
                this.ghichu = dr["ghichu"].ToString().Trim();
                this.maloai = dr["maloai"].ToString().Trim();
                this.tgbaohanh = int.Parse(dr["tgbaohanh"].ToString().Trim());
                
            }
        }
        //public HangHoa(string mahang, string mavach, string tenhang, string dvt, string mota, double gianhap, double giaxuatsi, double giaxuatle, int tondinhmuc, string ghichu, string maloai, int tgbaohanh, bool ngungtheodoi, int sltondauki, double tgtondauki)
        //{
        //    this.mahang = mahang;
        //    this.mavach = mavach;
        //    this.tenhang = tenhang;
        //    this.dvt = dvt;
        //    this.mota = mota;
        //    this.gianhap = gianhap;
        //    this.giaxuatsi = giaxuatsi;
        //    this.giaxuatle = giaxuatle;
        //    this.tondinhmuc = tondinhmuc;
           
        //    this.ghichu = ghichu;
        //    this.maloai = maloai;
        //    this.tgbaohanh = tgbaohanh;
        //    this.ngungtheodoi = ngungtheodoi;
        //    this.sltondauki = sltondauki;
        //    this.tgtondauki = tgtondauki;
        //}
        #endregion
        public Boolean isExistHangHoa(string mahang)
        {
            string sql = "select mahang from HangHoa where mahang = '" + mahang + "'";
            return isExist(sql);
        }
        public double laySLTonHienTai(string mahang)
        {
            string sql = "select sltonhientai from HangHoa where mahang = '" + mahang + "'";
            return (double)getNumberValue(sql);
        }
        public Boolean themHangHoa()
        {
            string sql = "insert into HangHoa(mahang,mavach,tenhang,dvt,dvt1,tilequydoi1,dvt2,tilequydoi2,mota,gianhap,giaxuatsi,giaxuatle,tondinhmuc,ghichu,tgbaohanh,maloai,sltonhientai,tgtonhientai) ";
            sql += "values ('" + mahang + "','" + mavach + "',N'" + tenhang + "',N'" + dvt + "',N'" + dvt1 + "'," + tilequydoi1 + ",N'" + dvt2 + "'," + tilequydoi2 + ",N'" + mota + "'," + gianhap + "," + giaxuatsi + "," + giaxuatle + "," + tondinhmuc + ",N'" + ghichu + "'," + tgbaohanh + ",'" + maloai + "'," + sltonhientai + "," + tgtonhientai + ")";
            //string sqlquydoi = "insert into DonViQuyDoi(mahang, dvtgoc,dvtquydoi,slquydoi) values ('" + mahang + "',N'" + dvt + "',N'" + dvt+ "',1)";
            //return excuteQueryTrans2(sqlhh, sqlquydoi);
            return executeQuery(sql);
           
        }
        public Boolean xoaHangHoa(string mahang)
        {
            string sql = "delete HangHoa where mahang = '" + mahang + "'";
            return executeQuery(sql);
        }
        public Boolean suaHangHoa(string mahangcu)
        {
            string sql = "update HangHoa set mahang = '" + mahang + "', mavach='" + mavach + "',tenhang=N'" + tenhang + "',dvt=N'" + dvt + "',dvt1 = N'" + dvt1 + "',tilequydoi1 = " + tilequydoi1 + ",dvt2 = N'" + dvt2 + "',tilequydoi2 = " + tilequydoi2 + ",mota=N'" + mota + "',gianhap=" + gianhap + ", ";
            sql += "giaxuatsi=" + giaxuatsi + ",giaxuatle=" + giaxuatle + ",tondinhmuc=" + tondinhmuc + ",ghichu=N'" + ghichu + "',tgbaohanh=" + tgbaohanh + ",maloai='" + maloai + "' where mahang = '" + mahangcu + "'";
           
            return executeQuery(sql);
        }
        public Boolean suaHangHoa1(string mahangcu)
        {
            string sql = "update HangHoa set mahang = '" + mahang + "', mavach='" + mavach + "',tenhang=N'" + tenhang + "',dvt=N'" + dvt + "',mota=N'" + mota + "',gianhap=" + gianhap + ", ";
            sql += "giaxuatsi=" + giaxuatsi + ",giaxuatle=" + giaxuatle + ",tondinhmuc=" + tondinhmuc + ",ghichu=N'" + ghichu + "',maloai='" + maloai + "' where mahang = '" + mahangcu + "'";

            return executeQuery(sql);
        }
        public DataTable timKiemHangHoa(string tukhoa)
        {
            //string sql = "select /*b.machungloai,*/ b.maloai/*,tenchungloai, tenloai*/,mahang/*,mavach*/,mahang +'__'+tenhang hanghoa,dvt,mota,gianhap,giaxuatsi, giaxuatle,chietkhau,tgbaohanh,ghichu,tondinhmuc,sltonhientai from ChungLoaiHang a, LoaiHang b, HangHoa c where a.machungloai = b.machungloai and b.maloai = c.maloai ";
            string sql = @"
                            select 
                                lh.maloai	                            
                                ,hh.mahang
	                            ,mahang +'__'+tenhang hanghoa
	                            ,dvt
	                            ,gianhap
	                            ,giaxuatle
	                            ,giaxuatsi
	                            ,dvt1
	                            ,tilequydoi1
	                            ,dvt2
	                            ,tilequydoi2
                                ,hh.ghichu
                                ,hh.tondinhmuc
                                ,hh.tgbaohanh
                                ,hh.mota
                            from
	                            HangHoa hh
                                ,loaihang lh
                            where
                                hh.maloai =lh.maloai
                                and lh.maloai+mahang+tenhang+dvt+gianhap+giaxuatle +giaxuatsi+hh.ghichu+hh.mota like N''";
            return getDataTable(sql);
        }

        public List<DT> LayDSHanghoa_FV()
        {
//            string sql = @"
//                            select 
//	                            mahang
//	                            ,mahang +'__'+tenhang hanghoa
//	                            ,dvt
//	                            ,gianhap
//	                            ,giaxuatle
//	                            ,giaxuatsi
//	                            ,dvt1
//	                            ,tilequydoi1
//	                            ,dvt2
//	                            ,tilequydoi2
//                            from
//	                            HangHoa
//                        ";
//            //return getDataTable(sql);
            return getDataTableSP("sp_Loadhanghoa");
        }

        public DataTable layDSHangHoa()
        {
            string sql = "select distinct b.machungloai, b.maloai, tenloai,c.mahang + '   ' + tenhang as hanghoa,c.mahang,mavach,tenhang,dvt,mota,gianhap,giaxuatsi, giaxuatle,chietkhau,tgbaohanh,ghichu,tondinhmuc,d.sltonhientai, sltonhientaiquydoi, sltonhientaile from ChungLoaiHang a, LoaiHang b, HangHoa c, v_TonKhoHienTai d where a.machungloai = b.machungloai and b.maloai = c.maloai and c.mahang = d.mahang order by b.machungloai,maloai,tenhang";
            string sql1 = @"
                            select 
                                lh.maloai	                            
                                ,mahang
	                            ,tenhang
                                ,mahang+'_'+tenhang hanghoa
	                            ,dvt
	                            ,gianhap
	                            ,giaxuatle
	                            ,giaxuatsi
	                            ,dvt1
	                            ,tilequydoi1
	                            ,dvt2
	                            ,tilequydoi2
                                ,hh.ghichu
                                ,hh.tondinhmuc
                                ,hh.tgbaohanh
                                ,hh.mota
                            from
	                            HangHoa hh
                                ,loaihang lh
                            where
                                hh.maloai =lh.maloai
                            ";
            return getDataTable(sql1);
        }
        public DataTable layDSDonViTinh(string mahang)
        {
            string sql = "select dvtquydoi from DonViQuyDoi where mahang = '" + mahang + "'";
            return getDataTable(sql);
        }
        public DataTable layDSTonKhoHangHoaBanDau()
        {
            string sql = "select a.mahang, tenhang, a.dvt,isnull(slchuaquydoi,0) as slchuaquydoi,isnull(slquydoi,1) as slquydoi, isnull(slnhapxuat,0) as slnhapxuat, ";
            sql += "isnull(dgnhapxuat,gianhap) as dgnhapxuat, isnull(chietkhau,0) as chietkhau,isnull(thanhtien,0) as thanhtien ";
            sql += "from HangHoa a left join (SELECT * FROM PhieuNhapXuatCT where manhapxuat = 'PN20000101-001') b on a.mahang = b.mahang";
            return getDataTable(sql);
        }
        public DataTable timKiemTonKhoHangHoaBanDau(string tukhoa)
        {
            string sql = "select a.mahang, tenhang, a.dvt,isnull(slchuaquydoi,0) as slchuaquydoi,isnull(slquydoi,1) as slquydoi, isnull(slnhapxuat,0) as slnhapxuat, ";
            sql += "isnull(dgnhapxuat,gianhap) as dgnhapxuat, isnull(chietkhau,0) as chietkhau,isnull(thanhtien,0) as thanhtien ";
            sql += "from HangHoa a left join (SELECT * FROM PhieuNhapXuatCT where manhapxuat = 'PN20000101-001') b on a.mahang = b.mahang ";
            if (tukhoa.Trim().Length > 0)
                sql += "where a.mahang + ' ' + tenhang like N'%" + tukhoa + "%'";
            return getDataTable(sql);
        }
        public DataTable layBaoCaoNhapXuatChiTiet(string thongtindoanhnghiep, string tungay, string denngay,string mahang,string tenhang)
        {
            /*
            string sql = "select '" + mahang + "' + ' - ' + N'" + tenhang + "' as hanghoa,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian, ";
		    sql += "sochungtu,  ngaylap,  noidung, dgxuatvon,slnhaptrongki, slxuattrongki, sltoncuoiki,  tgtoncuoiki,id,sort,loai ";
            sql += "from ";
            sql += "( ";
            sql += "select null as sochungtu,null as ngaylap, N'Tồn đầu kỳ' as noidung,dgxuatvon,  null as slnhaptrongki, null as slxuattrongki,toncuoiki as sltoncuoiki,dgxuatvon * toncuoiki as tgtoncuoiki,id,sort,'dauki' as loai ";
            sql += "from v_BaoCaoTonKho where mahang = '" + mahang + "'  and id = (select max(id) from v_BaoCaoTonKho where mahang = '" + mahang + "' and convert(datetime,ngaynhapxuat,103) < convert(datetime,'" + tungay + "',103)) ";
            sql += "union all ";
            sql += "select manhapxuat as sochungtu,ngaynhapxuat as ngaylap,case when loainhapxuat = 'PN' then N'Nhập hàng' else N'Xuất hàng' end as noidung, dgxuatvon,  slnhap as slnhaptrongki, slxuat as slxuattrongki,toncuoiki as sltoncuoiki,dgxuatvon * toncuoiki as tgtoncuoiki,id,sort,loainhapxuat as loai ";
            sql += "from v_BaoCaoTonKho where mahang = '" + mahang + "' and convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            sql += ") temp ";
            sql += "order by sort";
             */
            string sql = "select '" + mahang + "' + ' - ' + N'" + tenhang + "' as hanghoa,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian, ";
            sql += "sochungtu,  ngaylap,  noidung, dgxuatvon,slnhaptrongki, slxuattrongki, sltoncuoiki,  tgtoncuoiki,id,sort,loai ";
            sql += "from ";
            sql += "( ";
            sql += "select null as sochungtu,null as ngaylap, N'Tồn đầu kỳ' as noidung,dgxuatvon,  null as slnhaptrongki, null as slxuattrongki,toncuoiki as sltoncuoiki,dgxuatvon * toncuoiki as tgtoncuoiki,id,sort,'dauki' as loainhapxuat, 0 as loai ";
            sql += "from v_BaoCaoTonKho where mahang = '" + mahang + "'  and id = (select max(id) from v_BaoCaoTonKho where mahang = '" + mahang + "' and convert(datetime,ngaynhapxuat,103) < convert(datetime,'" + tungay + "',103)) ";
            sql += "union all ";
            sql += "select manhapxuat as sochungtu,ngaynhapxuat as ngaylap,case when loainhapxuat = 'PN' then N'Nhập hàng' else N'Xuất hàng' end as noidung, dgxuatvon,  slnhap as slnhaptrongki, slxuat as slxuattrongki,toncuoiki as sltoncuoiki,dgxuatvon * toncuoiki as tgtoncuoiki,id,sort,loainhapxuat,  loai ";
            sql += "from v_BaoCaoTonKho where mahang = '" + mahang + "' and convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            sql += ") temp ";
            sql += "order by sort";
            return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay","@mahang","@tenhang" };
            //string[] strValue = { thongtindoanhnghiep, tungay, denngay,mahang,tenhang };
            //return getDataTableSP("sp_TonKhoHangHoaCT", strPara, strValue);
            
        }
        public DataSet layDSHangHoa1()
        {
            string sql = "select mahang,tenhang from HangHoa ";
            return getDataSet(sql);
        }
        public DataTable layThongTinHangHoa(string mahang)
        {
            string sql = "select a.maloai,a.mahang,mavach,tenhang,dvt,gianhap,giaxuatsi, giaxuatle,tgbaohanh, chietkhau,mota,ghichu,tondinhmuc,ghichu,sltonhientai,tgtonhientai from HangHoa a, LoaiHang b where a.mahang = '" + mahang + "' and a.maloai = b.maloai ";
            return getDataTable(sql);
        }
        public DataTable layDSDonDatHangCuaHH(string mahang)
        {
            string sql = "select * from DonDatHangCT where mahang = '" + mahang + "'";
            return getDataTable(sql);
        }
        public DataTable layDSPhieuNhapXuatCuaHH(string mahang)
        {
            string sql = "select * from PhieuNhapXuatCT where mahang = '" + mahang + "'";
            return getDataTable(sql);
        }
        public Boolean capNhatGiaNhapXuat(string mahang, double giaxuatle, double giaxuatsi)
        {
            string sql = "update HangHoa set giaxuatle = " + giaxuatle + ", giaxuatsi = " + giaxuatsi + " where mahang = '" + mahang + "'";
            return executeQueryTrans(sql);
        }
        public Boolean capNhatTonDauKi(string mahang, int sltondauki, double tgtondauki)
        {
            string sql = "update HangHoa set sltondauki = " + sltondauki + ", tgtondauki = " + tgtondauki + " where mahang = '" + mahang + "'";
            return executeQueryTrans(sql);
        }
        public string taoMaHang()
        {
            string sql = "select max(convert(int,mahang)) from HangHoa";
            return string.Format("{0,6:000000}", getNumberValue(sql) + 1);
        }
        public double laySLTonKhoHienTai(string mahang, int thang, double nam)
        {
           
            string sql = "select sltonhientai from v_TonKhoHienTai where mahang = '" + mahang + "'";
            return getNumberValue(sql);
        }
        public DataTable InReportHangHoa(string thongtindoanhnghiep,string maloai)
        {
            string sql = "select isnull(N'" + thongtindoanhnghiep + "','') as thongtindoanhnghiep,tenchungloai, b.maloai, tenloai,mahang,mavach,tenhang,dvt,mota,gianhap,giaxuatsi, giaxuatle,chietkhau,tgbaohanh,ghichu,tondinhmuc from ChungLoaiHang a, LoaiHang b, HangHoa c where a.machungloai = b.machungloai and b.maloai = c.maloai ";
            if (maloai.Trim() != "")
                sql += "and b.maloai = '" + maloai + "' ";
            sql += "order by maloai,tenhang";
            return getDataTable(sql);
        }
        //public DataTable layTonKhoTheoChungLoai(string thongtindoanhnghiep, string tungay, string denngay, string machungloai)
        //{
           
        //    DataTable dt = new DataTable();

        //    if (machungloai.Equals("") == true)
        //    {
        //        string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay" };
        //        string[] strValue = { thongtindoanhnghiep, tungay, denngay };
        //        dt = getDataTableSP("sp_TonKhoTatCaCacChungLoaiVaLoaiHang", strPara, strValue);

        //    }
        //    else
        //    {
        //        string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay","@machungloai" };
        //        string[] strValue = { thongtindoanhnghiep, tungay, denngay,machungloai };
        //        dt = getDataTableSP("sp_TonKhoTheoChungLoai", strPara, strValue);
        //    }
        //    return dt;
        //}
        //public DataTable layTonKhoTheoLoai(string thongtindoanhnghiep, string tungay, string denngay, string maloai)
        //{

        //    DataTable dt = new DataTable();

        //    if (maloai.Equals("") == true)
        //    {
        //        string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay" };
        //        string[] strValue = { thongtindoanhnghiep, tungay, denngay };
        //        dt = getDataTableSP("sp_TonKhoTatCaCacChungLoaiVaLoaiHang", strPara, strValue);

        //    }
        //    else
        //    {
        //        string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay", "@maloai" };
        //        string[] strValue = { thongtindoanhnghiep, tungay, denngay, maloai };
        //        dt = getDataTableSP("sp_TonKhoTheoLoai", strPara, strValue);
        //    }
        //    return dt;
        //}
        public DataTable baoCaoLoiNhuanTheoLoaiHang(string thongtindoanhnghiep, string tungay, string denngay)
        {
            string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian,N'BÁO CÁO LỢI NHUẬN THEO HÀNG HÓA' as loaibaocao , ";
            sql += "tenloai as tieuchinhom,convert(varchar(10),ngaynhapxuat, 103) as ngaynhapxuat,manhapxuat as chungtu, a.mahang + ' - ' + a.tenhang as tenhang,a.dvt, dgxuatvon as gianhaptb,slchuaquydoi as slban,thanhtienvon,doanhso,chenhlech as loinhuan,loai ";
            sql += "from ";
            sql += "( ";
            sql += "select ngaynhapxuat,manhapxuat, mahang,tenhang,dvt, dgxuatvon,slchuaquydoi,thanhtienvon,doanhso,chenhlech,loai ";
            sql += "from v_PhieuNhapXuat where daxoa = 'false' and ";
            sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            sql += "and loai = 4 ";
            sql += ") a left join HangHoa b on a.mahang = b.mahang left join LoaiHang c on b.maloai = c.maloai ";
            //sql += " union all ";
            //sql += "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian,N'BÁO CÁO LỢI NHUẬN THEO HÀNG HÓA' as loaibaocao , ";
            //sql += "tenloai as tieuchinhom,convert(varchar(10),ngaynhapxuat, 103) as ngaynhapxuat,manhapxuat as chungtu, mabosp as mahang,tenbosp as tenhang,a.dvt, dgxuatvon as gianhaptb,slchuaquydoi as slban,thanhtienvon,doanhso,chenhlech as loinhuan,loai ";
            //sql += "from ";
            //sql += "(  ";
            //sql += "select ngaynhapxuat,a.manhapxuat, b.mabosp,tenbosp,b.dvt,gianhap as dgxuatvon,soluong as slchuaquydoi,gianhap * soluong as thanhtienvon,thanhtien as doanhso,thanhtien - (gianhap * soluong) as chenhlech,maloai,loai  ";
            //sql += "from PhieuNhapXuat a, BoSanPhamNhapXuat b, BoSanPham c where  a.manhapxuat = b.manhapxuat and b.mabosp = c.mabosp and  ";
            //sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            //sql += "and loai = 5  ";
            //sql += ") a  left join LoaiHang b on a.maloai = b.maloai ";
            sql += "order by tieuchinhom";
            return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay" };
            //string[] strValue = { thongtindoanhnghiep, tungay, denngay };
            //return getDataTableSP("sp_LoiNhuanTheoLoaiHang", strPara, strValue);
        }
        public DataTable baoCaoLoiNhuanTheoKhachHang(string thongtindoanhnghiep, string tungay, string denngay)
        {
            string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian,N'BÁO CÁO LỢI NHUẬN THEO KHÁCH HÀNG' as loaibaocao , ";
            sql += "tenkhncc as tieuchinhom,convert(varchar(10),ngaynhapxuat, 103) as ngaynhapxuat,manhapxuat as chungtu, a.mahang,a.tenhang,a.dvt, dgxuatvon as gianhaptb,slchuaquydoi as slban,thanhtienvon,doanhso,chenhlech as loinhuan,loai ";
            sql += "from ";
            sql += "( ";
            sql += "select tenkhncc,ngaynhapxuat,manhapxuat, mahang,tenhang,dvt, dgxuatvon,slchuaquydoi,thanhtienvon,doanhso,chenhlech,loai ";
            sql += "from v_PhieuNhapXuat where daxoa = 'false' and ";
            sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            sql += "and loai = 4 ";
            sql += ") a ";
            //sql += "union all ";
            //sql += "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian,N'BÁO CÁO LỢI NHUẬN THEO HÀNG HÓA' as loaibaocao , ";
            //sql += "tenkhncc as tieuchinhom,convert(varchar(10),ngaynhapxuat, 103) as ngaynhapxuat,manhapxuat as chungtu, mabosp as mahang,tenbosp as tenhang,a.dvt, dgxuatvon as gianhaptb,slchuaquydoi as slban,thanhtienvon,doanhso,chenhlech as loinhuan,loai ";
            //sql += "from ";
            //sql += "(  ";
            //sql += "select ngaynhapxuat,a.manhapxuat, b.mabosp,tenbosp,b.dvt,gianhap as dgxuatvon,soluong as slchuaquydoi,gianhap * soluong as thanhtienvon,thanhtien as doanhso,thanhtien - (gianhap * soluong) as chenhlech,makhncc,loai  ";
            //sql += "from PhieuNhapXuat a, BoSanPhamNhapXuat b, BoSanPham c where  a.manhapxuat = b.manhapxuat and b.mabosp = c.mabosp and  ";
            //sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            //sql += "and loai = 5  ";
            //sql += ") a  left join KhachHangNCC b on a.makhncc = b.makhncc ";
            sql += "order by tieuchinhom";
            return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay" };
            //string[] strValue = { thongtindoanhnghiep, tungay, denngay };
            //return getDataTableSP("sp_LoiNhuanTheoKhachHang", strPara, strValue);
        }
        public DataTable baoCaoLoiNhuanTheoNhanVien(string thongtindoanhnghiep, string tungay, string denngay)
        {
            string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian,N'BÁO CÁO LỢI NHUẬN THEO NHÂN VIÊN' as loaibaocao , ";
            sql += "hoten as tieuchinhom,convert(varchar(10),ngaynhapxuat, 103) as ngaynhapxuat,manhapxuat as chungtu, a.mahang,a.tenhang,a.dvt, dgxuatvon as gianhaptb,slchuaquydoi as slban,thanhtienvon,doanhso,chenhlech as loinhuan,loai ";
            sql += "from ";
            sql += "( ";
            sql += "select tendn,ngaynhapxuat,manhapxuat, mahang,tenhang,dvt, dgxuatvon,slchuaquydoi,thanhtienvon,doanhso,chenhlech,loai ";
            sql += "from v_PhieuNhapXuat where daxoa = 'false' and ";
            sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
            sql += "and loai = 4 ";
            sql += ") a left join HangHoa b on a.mahang = b.mahang left join NguoiDung c on a.tendn = c.tendn ";
            //sql += "union all ";
            //sql += "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian,N'BÁO CÁO LỢI NHUẬN THEO HÀNG HÓA' as loaibaocao , ";
            //sql += "hoten as tieuchinhom,convert(varchar(10),ngaynhapxuat, 103) as ngaynhapxuat,manhapxuat as chungtu, mabosp as mahang,tenbosp as tenhang,a.dvt, dgxuatvon as gianhaptb,slchuaquydoi as slban,thanhtienvon,doanhso,chenhlech as loinhuan,loai ";
            //sql += "from ";
            //sql += "(  ";
            //sql += "select ngaynhapxuat,a.manhapxuat, b.mabosp,tenbosp,b.dvt,gianhap as dgxuatvon,soluong as slchuaquydoi,gianhap * soluong as thanhtienvon,thanhtien as doanhso,thanhtien - (gianhap * soluong) as chenhlech,tendn,loai  ";
            //sql += "from PhieuNhapXuat a, BoSanPhamNhapXuat b, BoSanPham c where  a.manhapxuat = b.manhapxuat and b.mabosp = c.mabosp and  ";
            //sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            //sql += "and loai = 5  ";
            //sql += ") a  left join NguoiDung b on a.tendn = b.tendn ";
            sql += "order by tieuchinhom";
            return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@tungay", "@denngay" };
            //string[] strValue = { thongtindoanhnghiep, tungay, denngay };
            //return getDataTableSP("sp_LoiNhuanTheoNhanVien", strPara, strValue);
        }
        public DataTable timKiemHangHoa(string machungloai, string maloai, string mahang, string tenhang)
        {
            string sql = "select tenchungloai, b.maloai, tenloai,mahang,mavach,tenhang,dvt,mota,gianhap,giaxuatsi, giaxuatle,chietkhau,thue,tgbaohanh,ghichu,tondinhmuc from ChungLoaiHang a, LoaiHang b, HangHoa c where a.machungloai = b.machungloai and b.maloai = c.maloai ";
            if (machungloai.Equals("") == false)
                sql += "and a.machungloai = '" + machungloai + "' ";
            if (maloai.Equals("") == false)
                sql += "and b.maloai = '" + maloai + "' ";
            if (mahang.Equals("") == false)
                sql += "and c.mahang = '" + mahang + "' ";
            if (tenhang.Equals("") == false)
                sql += "and tenhang = N'" + tenhang + "' ";
            return getDataTable(sql);

        }
        //public DataTable baoCaoTonKhoTheoChungLoai(string thongtindoanhnghiep, string tungay, string denngay, string machungloai)
        //{

        //    string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,'Từ ngày: " + tungay + " - đến ngày: " +  denngay +  "' as khoangthoigian,tenchungloai,tenloai,temp1.mahang, tenhang,dvt,temp1.sltondauki,temp1.tgtondauki,slnhaptrongki, tgnhaptrongki,slxuattrongki,tgxuattrongki, ";
        //    sql += "(temp1.sltondauki + slnhaptrongki - slxuattrongki ) as sltoncuoiki, ";

        //    sql += "gianhap * (case when (temp1.sltondauki + slnhaptrongki - slxuattrongki)   < 0 then 0 else (temp1.sltondauki + slnhaptrongki - slxuattrongki) end ) as tgtoncuoiki ";
        //    sql += "from (select mahang,tenhang,dvt,gianhap, a.maloai,tenloai,b.machungloai,tenchungloai from HangHoa a, LoaiHang b, ChungLoaiHang c where a.maloai = b.maloai and b.machungloai = c.machungloai ";
        //    if (machungloai.Trim().Equals("") == false)
        //    {
        //        sql += "and b.machungloai = '" + machungloai + "'";
        //    }
        //    sql += ") temp ";
        //    sql += "left join ";
        //    sql += "(select hh.mahang,(slnhap - slxuat) as sltondauki,isnull((slnhap - slxuat) * gianhap,0) as tgtondauki ";
        //    sql += "from HangHoa hh ";
        //    sql += "left join ";
        //    sql += "(select a.mahang,isnull(slnhap,0) as slnhap, isnull(tgnhap,0) as tgnhap  ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slnhap, sum(thanhtien) as tgnhap ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) nhap ";
        //    sql += "on hh.mahang = nhap.mahang ";
        //    sql += "left join ";
        //    sql += "(select a.mahang,isnull(slxuat,0) as slxuat, isnull(tgxuat,0) as tgxuat ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slxuat, sum(thanhtien) as tgxuat ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) xuat ";
        //    sql += "on hh.mahang = xuat.mahang) temp1 ";
        //    sql += "on temp.mahang = temp1.mahang left join ";
        //    sql += "(select a.mahang,isnull(slnhaptrongki,0) as slnhaptrongki, isnull(tgnhaptrongki,0) as tgnhaptrongki  ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slnhaptrongki, sum(thanhtien) as tgnhaptrongki ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and  ";
        //    sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
        //    sql += " group by mahang) b on a.mahang = b.mahang) temp2 ";
        //    sql += "on temp.mahang = temp2.mahang left join ";
        //    sql += "(select a.mahang,isnull(slxuattrongki,0) as slxuattrongki, isnull(tgxuattrongki,0) as tgxuattrongki  ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slxuattrongki, sum(thanhtien) as tgxuattrongki ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and  ";
        //    sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
        //    sql += "group by mahang) b on a.mahang = b.mahang) temp3 ";
        //    sql += "on temp.mahang = temp3.mahang order by tenloai";
        //    return getDataTable(sql);

        //}
        //public DataTable baoCaoTonKhoTheoLoaiHang(string thongtindoanhnghiep, string tungay, string denngay, string maloai)
        //{

        //    string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,'Từ ngày: " + tungay + " - đến ngày: " + denngay + "' as khoangthoigian,tenchungloai,tenloai,temp1.mahang, tenhang,dvt,temp1.sltondauki,temp1.tgtondauki,slnhaptrongki, tgnhaptrongki,slxuattrongki,tgxuattrongki, ";
        //    sql += "(temp1.sltondauki + slnhaptrongki - slxuattrongki ) as sltoncuoiki, ";

        //    sql += "gianhap * (case when (temp1.sltondauki + slnhaptrongki - slxuattrongki)   < 0 then 0 else (temp1.sltondauki + slnhaptrongki - slxuattrongki) end ) as tgtoncuoiki ";
        //    sql += "from (select mahang,tenhang,dvt,gianhap, a.maloai,tenloai,b.machungloai,tenchungloai from HangHoa a, LoaiHang b, ChungLoaiHang c where a.maloai = b.maloai and b.machungloai = c.machungloai ";
        //    if (maloai.Trim().Equals("") == false)
        //    {
        //        sql += "and a.machungloai = '" + maloai + "'";
        //    }
        //    sql += ") temp ";
        //    sql += "left join ";
        //    sql += "(select hh.mahang,(slnhap - slxuat) as sltondauki,isnull((slnhap - slxuat) * gianhap,0) as tgtondauki ";
        //    sql += "from HangHoa hh ";
        //    sql += "left join ";
        //    sql += "(select a.mahang,isnull(slnhap,0) as slnhap, isnull(tgnhap,0) as tgnhap  ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slnhap, sum(thanhtien) as tgnhap ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) nhap ";
        //    sql += "on hh.mahang = nhap.mahang ";
        //    sql += "left join ";
        //    sql += "(select a.mahang,isnull(slxuat,0) as slxuat, isnull(tgxuat,0) as tgxuat ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slxuat, sum(thanhtien) as tgxuat ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) <convert(datetime,'" + tungay + "',103) group by mahang) b on a.mahang = b.mahang) xuat ";
        //    sql += "on hh.mahang = xuat.mahang) temp1 ";
        //    sql += "on temp.mahang = temp1.mahang left join ";
        //    sql += "(select a.mahang,isnull(slnhaptrongki,0) as slnhaptrongki, isnull(tgnhaptrongki,0) as tgnhaptrongki  ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slnhaptrongki, sum(thanhtien) as tgnhaptrongki ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and  ";
        //    sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
        //    sql += " group by mahang) b on a.mahang = b.mahang) temp2 ";
        //    sql += "on temp.mahang = temp2.mahang left join ";
        //    sql += "(select a.mahang,isnull(slxuattrongki,0) as slxuattrongki, isnull(tgxuattrongki,0) as tgxuattrongki  ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,sum(slnhapxuat) as slxuattrongki, sum(thanhtien) as tgxuattrongki ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and  ";
        //    sql += "convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
        //    sql += "group by mahang) b on a.mahang = b.mahang) temp3 ";
        //    sql += "on temp.mahang = temp3.mahang order by tenloai";
        //    return getDataTable(sql);

        //}
        public DataTable layDSHangTonDuoiDinhMuc(string thongtindoanhnghiep,string machungloai, string maloai)
        {
            string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,mahang,tenhang,dvt,gianhap,giaxuatsi,giaxuatle,sltonhientai,tondinhmuc from v_HangTonDuoiDinhMuc where machungloai = (case when '" + machungloai + "' = '' then machungloai else '" + machungloai + "' end ) ";
            sql += "and maloai = (case when '" + maloai + "' = '' then maloai else '" + maloai + "' end )";
            return getDataTable(sql);
        }
        public DataTable layDSHangTonDuoiDinhMucCanNhap(string dsmahang)
        {
            string sql = "select mahang,tenhang,dvt,slchuaquydoi, slquydoi, slnhapxuat, dgnhapxuat, chietkhau,thuexuat,thanhtien from v_HangTonDuoiDinhMuc where mahang in (" + dsmahang + ")";
            return getDataTable(sql);
        }
        public Boolean suaMaHang(string mahangcu, string mahangmoi)
        {
            string sql = "update HangHoa set mahang = '" + mahangmoi + "' where mahang = '" + mahangcu + "'";
            return executeQuery(sql);
        }
        public DataTable layDSDVT()
        {
            string sql = "select distinct dvt from ((select distinct dvt from HangHoa) union all (select distinct dvt1 from HangHoa) union all (select distinct dvt2 from HangHoa)) temp";
            return getDataTable(sql);
        }
        public DataTable layDVTHangHoa(string mahang)
        {


            string sql = "select dvt, dvt1,tilequydoi1,dvt2,tilequydoi2 from HangHoa where mahang = '" + mahang + "'";
            //foreach (DataRow dr in getDataTable(sql).Rows)
            //{
            //    //DataRow row1 = new DataRow();
            //    //row1[0] = dr["dvtgoc"].ToString();
            //    //row1[1] = 1;
            //    //dt.Rows.InsertAt(row1, dt.Rows.Count);
            //    //DataRow row2 = new DataRow();
            //    //row2[0] = dr["dvtphu1"].ToString();
            //    //row2[1] = int.Parse(dr["tilequydoi1"].ToString());
            //    //dt.Rows.InsertAt(row2, dt.Rows.Count);
            //}
            //return dt;
            return getDataTable(sql);
        }

        public DataTable layThongTinQuyDoi(string mahang, string dvt,string loainhapxuat, Boolean giasi)
        {
            int tilequydoi = 1;
            string gia = "";
            if (loainhapxuat == "PN")
                gia = "gianhap";
            else
            {
                if (giasi)
                    gia = "giaxuatsi";
                else gia = "giaxuatle";
            }
            string sql = "select dvt, dvt1,tilequydoi1,dvt2,tilequydoi2 from HangHoa where mahang = '" + mahang + "'";
            foreach (DataRow dr in getDataTable(sql).Rows)
            {
                if (dr["dvt"].ToString().Trim().Equals(dvt))
                    tilequydoi = 1;
                if (dr["dvt1"].ToString().Trim().Equals(dvt))
                    tilequydoi = int.Parse(dr["tilequydoi1"].ToString());
                if (dr["dvt2"].ToString().Trim().Equals(dvt))
                    tilequydoi = int.Parse(dr["tilequydoi2"].ToString());

            }
            string sql1 = "select " + tilequydoi + " as slquydoi," + gia + " * " + tilequydoi + " as dgquydoi from HangHoa where mahang = '" + mahang + "'";
            return getDataTable(sql1);
        }

        internal bool isExistHangHoa()
        {
            throw new NotImplementedException();
        }
        public Boolean kiemTraHangTonDuoiDM(string mahang)
        {
            string sql = "select distinct a.mahang from v_TonKhoHienTai a left join HangHoa b on a.mahang = b.mahang where a.mahang = '" + mahang + "' and a.sltonhientai < tondinhmuc ";
            return isExist(sql);
        }
        public DataTable layThongTinKhuyenMai(string mahang, DateTime ngaylap)
        {
            string sql = "select distinct a.madot,slmua,slkhuyenmai from DotKhuyenMai a, DotKhuyenMaiCT b ";
            sql += "where mahang = '" + mahang + "' and convert(DateTime,'" + ngaylap.ToString("dd/MM/yyyy") + "',103) <= convert(DateTime,ngaykt,103) ";
            sql += "and a.madot = b.madot and a.madot =  ";
            sql += "(select distinct max(a.madot) from DotKhuyenMai a, DotKhuyenMaiCT b  ";
            sql += "where mahang = '" + mahang + "' and convert(DateTime,'" + ngaylap.ToString("dd/MM/yyyy") + "',103) <= convert(DateTime,ngaykt,103) ";
            sql += "and a.madot = b.madot)";
            return getDataTable(sql);
        }
    }
}
